import logging
from typing import Dict, Any, List, Optional
import json
from sqlalchemy import text

from tools.juyuan_tools.db.oracle_db import init_oracle_db, get_oracle_db
from tools.juyuan_tools.sql.base_info import match_fund, get_fund_basic_info
from tools.juyuan_tools.sql.fund_manager import get_manager_info, get_fund_by_manager
from tools.juyuan_tools.sql.net_value import get_net_value, get_fund_scale
from tools.juyuan_tools.sql.performance import get_fund_performance
from tools.juyuan_tools.sql.position import get_top_ten_stocks, get_top_five_bonds, get_all_stocks, get_all_bonds, \
    get_stocks_change

logger = logging.getLogger(__name__)


class OracleDataService:
    """Oracle数据服务"""

    def __init__(self):
        """初始化Oracle数据库连接"""
        self.db_session = None
        self._initialize_connection()

    def _initialize_connection(self):
        """初始化数据库连接"""
        try:
            # 初始化Oracle数据库
            init_oracle_db()
            # 获取Oracle数据库连接
            db_gen = get_oracle_db()
            self.db_session = next(db_gen)
            logger.info("Oracle数据库连接初始化成功")
        except Exception as e:
            logger.error(f"初始化Oracle数据库连接时发生错误: {str(e)}")
            self.db_session = None

    def _get_db_session(self):
        """获取数据库会话，如果连接失效则重新创建"""
        try:
            # 检查现有连接是否有效
            if self.db_session:
                # 尝试执行一个简单的查询来测试连接
                self.db_session.execute(text("SELECT 1 FROM DUAL"))
                return self.db_session
        except Exception as e:
            logger.warning(f"数据库连接失效，正在重新创建连接: {str(e)}")
        
        # 重新初始化连接
        try:
            self._initialize_connection()
            return self.db_session
        except Exception as e:
            logger.error(f"重新初始化数据库连接时发生错误: {str(e)}")
            return None

    def execute_query(self, sql: str, params: Dict[str, Any]) -> List[Dict[str, Any]]:
        """
        执行SQL查询
        """
        try:
            
            # 获取数据库会话
            db = self._get_db_session()
            if db is None:
                logger.error("无法获取有效的数据库连接")
                return []

            try:
                # 执行SQL查询
                result = db.execute(text(sql), params)
                # 获取列名
                columns = result.keys()
                # 获取结果
                rows = result.fetchall()

                # 将结果转换为字典列表
                result_list = []
                for row in rows:
                    result_dict = {}
                    for i, column in enumerate(columns):
                        result_dict[column] = row[i]
                    result_list.append(result_dict)

                return result_list
            finally:
                # 注意：这里不关闭连接，因为我们想要复用它
                # 只有在连接失效时才会重新创建
                pass
        except Exception as e:
            logger.error(f"执行SQL查询时发生错误: {str(e)}")
            return []

    # 基金基础信息查询方法
    def get_fund_basic_info(self, fund_code: Optional[str] = None, fund_name: Optional[str] = None) -> List[Dict[str, Any]]:
        """获取基金基本信息"""
        try:
            sql, params = get_fund_basic_info(fund_code, fund_name)
            return self.execute_query(sql, params)
        except Exception as e:
            logger.error(f"获取基金基本信息时发生错误: {str(e)}")
            return []

    def match_fund(self, fund_code: Optional[str] = None, fund_name: Optional[str] = None) -> List[Dict[str, Any]]:
        """匹配基金代码和名称"""
        try:
            sql, params = match_fund(fund_code, fund_name)
            return self.execute_query(sql, params)
        except Exception as e:
            logger.error(f"匹配基金时发生错误: {str(e)}")
            return []

    # 基金经理相关查询方法
    def get_manager_info(self, innercode: int) -> List[Dict[str, Any]]:
        """获取基金经理信息"""
        try:
            sql, params = get_manager_info(innercode)
            return self.execute_query(sql, params)
        except Exception as e:
            logger.error(f"获取基金经理信息时发生错误: {str(e)}")
            return []

    def get_fund_by_manager(self, manager_name: str) -> List[Dict[str, Any]]:
        """根据管理人查询基金"""
        try:
            sql, params = get_fund_by_manager(manager_name)
            return self.execute_query(sql, params)
        except Exception as e:
            logger.error(f"根据管理人查询基金时发生错误: {str(e)}")
            return []


    # 净值与规模查询方法
    def get_net_value(
            self,
            fund_code: Optional[str] = None,
            fund_name: Optional[str] = None,
            fund_short_name: Optional[str] = None,
            start_date: Optional[str] = None,
            end_date: Optional[str] = None
    ) -> List[Dict[str, Any]]:
        """获取基金净值相关信息（包含单位净值和复权单位净值）"""
        try:
            sql, params = get_net_value(fund_code, fund_name, fund_short_name, start_date, end_date)
            return self.execute_query(sql, params)
        except Exception as e:
            logger.error(f"获取基金净值相关信息（包含单位净值和复权单位净值）时发生错误: {str(e)}")
            return []

    def get_fund_scale(self, innercode: int) -> List[Dict[str, Any]]:
        """获取基金规模变化"""
        try:
            sql, params = get_fund_scale(innercode)
            return self.execute_query(sql, params)
        except Exception as e:
            logger.error(f"获取基金规模变化时发生错误: {str(e)}")
            return []

    # 基金业绩表现查询方法
    def get_fund_performance(self, fund_code: Optional[str] = None, fund_name: Optional[str] = None, start_date: Optional[str] = None, end_date: Optional[str] = None) -> List[Dict[str, Any]]:
        """获取基金业绩表现"""
        try:
            sql, params = get_fund_performance(fund_code, fund_name, start_date, end_date)
            return self.execute_query(sql, params)
        except Exception as e:
            logger.error(f"获取基金业绩表现时发生错误: {str(e)}")
            return []

    # 基金持仓查询方法
    def get_top_ten_stocks(self, fund_code: Optional[str] = None, fund_name: Optional[str] = None, start_date: Optional[str] = None, end_date: Optional[str] = None) -> List[Dict[str, Any]]:
        """获取十大重仓股"""
        try:
            sql, params = get_top_ten_stocks(fund_code, fund_name, start_date, end_date)
            return self.execute_query(sql, params)
        except Exception as e:
            logger.error(f"获取十大重仓股时发生错误: {str(e)}")
            return []

    def get_top_five_bonds(self, fund_code: Optional[str] = None, fund_name: Optional[str] = None, start_date: Optional[str] = None, end_date: Optional[str] = None) -> List[Dict[str, Any]]:
        """获取前五大债券持仓"""
        try:
            sql, params = get_top_five_bonds(fund_code, fund_name, start_date, end_date)
            return self.execute_query(sql, params)
        except Exception as e:
            logger.error(f"获取前五大债券持仓时发生错误: {str(e)}")
            return []

    def get_all_stocks(self, fund_code: Optional[str] = None, fund_name: Optional[str] = None, start_date: Optional[str] = None, end_date: Optional[str] = None) -> List[Dict[str, Any]]:
        """获取全部股票持仓"""
        try:
            sql, params = get_all_stocks(fund_code, fund_name, start_date, end_date)
            return self.execute_query(sql, params)
        except Exception as e:
            logger.error(f"获取全部股票持仓时发生错误: {str(e)}")
            return []

    def get_all_bonds(self, fund_code: Optional[str] = None, fund_name: Optional[str] = None, start_date: Optional[str] = None, end_date: Optional[str] = None) -> List[Dict[str, Any]]:
        """获取全部债券持仓"""
        try:
            sql, params = get_all_bonds(fund_code, fund_name, start_date, end_date)
            return self.execute_query(sql, params)
        except Exception as e:
            logger.error(f"获取全部债券持仓时发生错误: {str(e)}")
            return []

    def get_stocks_change(self, fund_code: Optional[str] = None, fund_name: Optional[str] = None, start_date: Optional[str] = None, end_date: Optional[str] = None) -> List[Dict[str, Any]]:
        """获取股票持仓变动"""
        try:
            sql, params = get_stocks_change(fund_code, fund_name, start_date, end_date)
            return self.execute_query(sql, params)
        except Exception as e:
            logger.error(f"获取股票持仓变动时发生错误: {str(e)}")
            return []